package com.example.layui_project.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.IndexedColors;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.support.ExcelTypeEnum;

public class EasyExcelUtil {
    /**
     * 单个sheet的生成（该方法需要实体类继承BaseRowModel）
     * 
     * @param os
     * @param clazz
     * @param data
     * @param sheetName
     * @param columnWidth
     * @return
     */
    public static Boolean writeExcel(OutputStream os, Class<? extends BaseRowModel> clazz,
            List<? extends BaseRowModel> data, String sheetName, Map<Integer, Integer> columnWidth) {
        try {
            /**
             * @param outputStream 文件输出流
             * @param typeEnum 输出文件类型03或07，强烈建议使用07版（可以输出超大excel而不内存溢出）
             * @param needHead 是否需要表头
             */
            ExcelWriter writer = new ExcelWriter(os, ExcelTypeEnum.XLSX, true);
            // 创建一个sheet
            Sheet sheet1 = new Sheet(1, 0, clazz);
            // 设置sheet
            sheet1.setSheetName(sheetName);
            if (BestUitls.isNotBlank(columnWidth)) {
                // sheet1.setColumnWidthMap(columnWidth);
                // sheet1.setAutoWidth(Boolean.TRUE);
            }
            sheet1.setTableStyle(createTableStyle());
            // 写入sheet
            writer.write(data, sheet1);
            writer.finish();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return false;
        } finally {
            // 关闭流
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return true;
    }

    public static Boolean writeExcelWithOutModel(OutputStream os, List<String> head, List<List<String>> rows,
            String sheetName) {
        ExcelWriter writer = new ExcelWriter(os, ExcelTypeEnum.XLSX, true);
        try {
            // 创建一个sheet
            Sheet sheet1 = new Sheet(1, 0);
            // 设置sheet
            sheet1.setSheetName(sheetName);
            sheet1.setTableStyle(createTableStyle());
            List<List<String>> headInfo = new ArrayList<List<String>>();
            if (BestUitls.isNotBlank(head)) {
                for (String heads : head) {
                    List<String> singletonList = Collections.singletonList(heads);
                    headInfo.add(singletonList);
                }
                sheet1.setHead(headInfo);
            }
            // 写入sheet
            writer.write0(rows, sheet1);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return false;
        } finally {
            // 关闭流
            if (writer != null) {
                writer.finish();
            }

            if (os != null) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return true;
    }

    /**
     * 获取所有的sheet
     * 
     * @param inputStream
     * @return
     */
    public static List<Sheet> getAllSheets(InputStream inputStream) {
        try {
            // 解析监听器
            StringExcelListener excelListener = new StringExcelListener();
            // 创建文件流
            ExcelReader reader = EasyExcelFactory.getReader(inputStream, excelListener);
            List<Sheet> sheets = reader.getSheets();
            if (BestUitls.isNotBlank(sheets) && !sheets.isEmpty()) {
                return sheets;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return null;
    }

    public static List<List<String>> readExcel(InputStream inputStream, String sheetName) {
        // 解析监听器
        StringExcelListener excelListener = new StringExcelListener();
        try {
            // 创建文件流
            ExcelReader reader = EasyExcelFactory.getReader(inputStream, excelListener);
            List<Sheet> sheets = reader.getSheets();
            Sheet sheet = null;
            // 得到所有的sheet
            if (!BestUitls.isNotBlank(sheetName)) {
                // 若传递进来的sheetName是空的则默认读取第一个sheet
                sheet = sheets.get(0);
            } else {
                for (Sheet s : reader.getSheets()) {
                    if (s.getSheetName().endsWith(sheetName)) {
                        sheet = s;
                        break;
                    }
                }
            }
            // 开始读取excel数据
            reader.read(sheet);
            return excelListener.getDatas();
            // 返回明细数据
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }

    public static Map<String, List<List<String>>> readExcelAllSheet(InputStream inputStream) {
        // 解析监听器
        StringExcelListener excelListener = new StringExcelListener();
        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
        try {
            // 创建文件流
            ExcelReader reader = EasyExcelFactory.getReader(inputStream, excelListener);
            List<Sheet> sheets = reader.getSheets();
            for (int i = 0; i < sheets.size(); i++) {
                reader.read(sheets.get(i));
                List<List<String>> datas = excelListener.getDatas();
                maps.put(sheets.get(i).getSheetName(), datas);
            }
            // 开始读取excel数据
            return maps;
            // 返回明细数据
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }

    private static TableStyle createTableStyle() {
        TableStyle tableStyle = new TableStyle();
        tableStyle.setTableHeadBackGroundColor(IndexedColors.GREY_50_PERCENT);
        tableStyle.setTableContentBackGroundColor(IndexedColors.GREY_25_PERCENT);
        Font contentFont = new Font();
        contentFont.setFontName("黑体");
        contentFont.setFontHeightInPoints((short) 12);
        tableStyle.setTableContentFont(contentFont);
        Font headFont = new Font();
        headFont.setFontName("黑体");
        headFont.setFontHeightInPoints((short) 10);
        tableStyle.setTableHeadFont(headFont);
        return tableStyle;
    }

    public static void main(String[] args) {
        try {
            InputStream is = new FileInputStream(new File("C:\\Users\\luoxin\\Desktop\\2019年10月存量用户-open后台数据.xlsx"));
            List<List<String>> readExcel = EasyExcelUtil.readExcel(is, null);
            for (List<String> list : readExcel) {
                System.out.println(list);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

    }
}
